﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Xml;
using System.Xml.XPath;
using Common.Utilities;

namespace Common.Utilities.OpenXml
{
    /// <summary>
    /// 
    /// </summary>
    public class ExcelWorksheetUpdator : IDisposable
    {
        private readonly string m_ReportFilePath;
        private PackageHelper m_Package;
        private SharedStrings m_SharedString;

        /// <summary>
        /// 
        /// </summary>
        /// <param name="reportFilePath"></param>
        public ExcelWorksheetUpdator(string reportFilePath)
        {
            this.m_ReportFilePath = reportFilePath;
            FileStream fs = File.OpenRead(reportFilePath);
            byte[] contents = new byte[fs.Length];
            fs.Read(contents, 0, (int)fs.Length);
            fs.Close();
            m_Package = new PackageHelper(contents);
            m_SharedString = new SharedStrings(m_Package);
        }

        #region sheet data binding
        /// <summary>
        /// 
        /// </summary>
        /// <param name="worksheetName"></param>
        /// <param name="dt"></param>
        /// <param name="columnPositions"></param>
        /// <param name="headerRowPosition"></param>
        public void FillTable(string worksheetName, DataTable dt, Dictionary<string, int> columnPositions, int headerRowPosition)
        {
            {
                Uri sheetUri =
                    new Uri(string.Format(@"/xl/worksheets/{0}.xml", worksheetName), UriKind.Relative);
                XmlDocument sheetXml = m_Package.GetWritablePart(sheetUri);
                XPathNavigator sheetData =
                    sheetXml.CreateNavigator()
                        .SelectSingleNode(string.Format("{0}:worksheet/{0}:sheetData", Prefixes.SpreadsheetML), Namespaces.NamespaceManager);
                using (XmlWriter writer = sheetData.AppendChild())
                {
                    GridHelper gridHelper = new GridHelper(dt, m_SharedString);
                    gridHelper.WriteTo(writer, headerRowPosition, columnPositions);
                }
                m_Package.SavePart(sheetUri, sheetXml);
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="worksheetName"></param>
        /// <param name="dt"></param>
        /// <param name="columnPositions"></param>
        /// <param name="headerRowPosition"></param>
        /// <param name="keyFieldName"></param>
        /// <param name="keyColPosition"></param>
        public void FillTableByKey(string worksheetName,
            DataTable dt, Dictionary<string, int> columnPositions, int headerRowPosition,
            string keyFieldName, int keyColPosition)
        {

            {
                Uri sheetUri =
                    new Uri(string.Format(@"/xl/worksheets/{0}.xml", worksheetName), UriKind.Relative);
                XmlDocument sheetXml = m_Package.GetWritablePart(sheetUri);
                GridHelper gridHelper = new GridHelper(dt, m_SharedString);
                gridHelper.WriteByKey(sheetXml, headerRowPosition, columnPositions, keyFieldName, keyColPosition);
                m_Package.SavePart(sheetUri, sheetXml);
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="worksheetName"></param>
        /// <param name="rangeName"></param>
        /// <param name="value"></param>
        /// <param name="isShared"></param>
        /// <param name="spanFrom"></param>
        /// <param name="spanTo"></param>
        public void FillCellValue(string worksheetName,
            string rangeName, string value, bool isShared, int spanFrom, int spanTo)
        {
            {
                value = StringUtil.RemoveNonAsciiCharacters(value);
                Uri sheetUri =
                    new Uri(string.Format(@"/xl/worksheets/{0}.xml", worksheetName), UriKind.Relative);
                XmlDocument sheetXml = m_Package.GetWritablePart(sheetUri);
                int rowNumber = ExcelRangeHelper.GetRowIndex(rangeName);
                int colNumber = ExcelRangeHelper.GetColumnIndex(rangeName);
                string rowNodeXPath = string.Format("/{0}:worksheet/{0}:sheetData/{0}:row[@r=\"{1}\"]", Prefixes.SpreadsheetML, rowNumber);
                XmlNode rowNode = sheetXml.SelectSingleNode(rowNodeXPath, Namespaces.NamespaceManager);
                if (rowNode == null)
                {
                    rowNode = sheetXml.CreateElement(Prefixes.SpreadsheetML, "row", Namespaces.SpreadsheetML);
                    XmlNode sheetDataNode = sheetXml.SelectSingleNode(string.Format("/{0}:worksheet/{0}:sheetData", Prefixes.SpreadsheetML), Namespaces.NamespaceManager);
                    sheetDataNode.AppendChild(rowNode);
                    XmlAttribute rowNumAtt = sheetXml.CreateAttribute("r");
                    rowNode.Attributes.Append(rowNumAtt);
                    rowNumAtt.Value = rowNumber.ToString();
                    // update span 
                    XmlAttribute spanAttr = rowNode.Attributes.GetNamedItem("spans") as XmlAttribute;
                    if (spanAttr == null)
                    {
                        spanAttr = sheetXml.CreateAttribute("spans");
                        rowNode.Attributes.Append(spanAttr);
                        spanAttr.Value = string.Format("{0}:{1}", spanFrom, spanTo);
                    }
                }

                string columnName = ExcelRangeHelper.GetColumnName(colNumber);
                XmlNode cellNode = rowNode.SelectSingleNode(string.Format("{0}:c[@r=\"{1}{2}\"]", Prefixes.SpreadsheetML, columnName, rowNumber), Namespaces.NamespaceManager);
                if (cellNode == null)
                {
                    cellNode = sheetXml.CreateElement(Prefixes.SpreadsheetML, "c", Namespaces.SpreadsheetML);
                    rowNode.AppendChild(cellNode);
                }
                cellNode.RemoveAll();
                XmlAttribute rAttr = sheetXml.CreateAttribute("r");
                rAttr.Value = string.Format("{0}{1}", columnName, rowNumber);
                cellNode.Attributes.Append(rAttr);
                XmlNode vNode = sheetXml.CreateElement(Prefixes.SpreadsheetML, "v", Namespaces.SpreadsheetML);
                cellNode.AppendChild(vNode);
                if (isShared)
                {
                    XmlAttribute typeAttr = cellNode.Attributes.GetNamedItem("t") as XmlAttribute;
                    if (typeAttr == null)
                    {
                        typeAttr = sheetXml.CreateAttribute("t");
                        cellNode.Attributes.Append(typeAttr);
                    }
                    typeAttr.Value = "s";
                    int sharedStringIndex = m_SharedString.LookupString(value);
                    vNode.InnerText = sharedStringIndex.ToString();
                }
                else
                {
                    vNode.InnerText = value;
                }
                m_Package.SavePart(sheetUri, sheetXml);
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="worksheetName"></param>
        /// <param name="rangeName"></param>
        /// <param name="formular"></param>
        /// <param name="spanFrom"></param>
        /// <param name="spanTo"></param>
        public void FillCellFormular(string worksheetName,
            string rangeName, string formular,
            int spanFrom, int spanTo)
        {
            {
                Uri sheetUri =
                    new Uri(string.Format(@"/xl/worksheets/{0}.xml", worksheetName), UriKind.Relative);
                XmlDocument sheetXml = m_Package.GetWritablePart(sheetUri);
                int rowNumber = ExcelRangeHelper.GetRowIndex(rangeName);
                int colNumber = ExcelRangeHelper.GetColumnIndex(rangeName);
                string rowNodeXPath = string.Format("/{0}:worksheet/{0}:sheetData/{0}:row[@r=\"{1}\"]", Prefixes.SpreadsheetML, rowNumber);
                XmlNode rowNode = sheetXml.SelectSingleNode(rowNodeXPath, Namespaces.NamespaceManager);
                if (rowNode == null)
                {
                    rowNode = sheetXml.CreateElement(Prefixes.SpreadsheetML, "row", Namespaces.SpreadsheetML);
                    XmlNode sheetDataNode = sheetXml.SelectSingleNode(string.Format("/{0}:worksheet/{0}:sheetData", Prefixes.SpreadsheetML), Namespaces.NamespaceManager);
                    sheetDataNode.AppendChild(rowNode);
                    XmlAttribute rowNumAtt = sheetXml.CreateAttribute("r");
                    rowNode.Attributes.Append(rowNumAtt);
                    rowNumAtt.Value = rowNumber.ToString();
                    // update span 
                    XmlAttribute spanAttr = rowNode.Attributes.GetNamedItem("spans") as XmlAttribute;
                    if (spanAttr == null)
                    {
                        spanAttr = sheetXml.CreateAttribute("spans");
                        rowNode.Attributes.Append(spanAttr);
                        spanAttr.Value = string.Format("{0}:{1}", spanFrom, spanTo);
                    }
                }

                string columnName = ExcelRangeHelper.GetColumnName(colNumber);
                XmlNode cellNode = rowNode.SelectSingleNode(string.Format("{0}:c[@r=\"{1}{2}\"]", Prefixes.SpreadsheetML, columnName, rowNumber), Namespaces.NamespaceManager);
                if (cellNode == null)
                {
                    cellNode = sheetXml.CreateElement(Prefixes.SpreadsheetML, "c", Namespaces.SpreadsheetML);
                    rowNode.AppendChild(cellNode);
                }
                cellNode.RemoveAll();
                XmlAttribute rAttr = sheetXml.CreateAttribute("r");
                rAttr.Value = string.Format("{0}{1}", columnName, rowNumber);
                cellNode.Attributes.Append(rAttr);
                XmlNode fNode = sheetXml.CreateElement(Prefixes.SpreadsheetML, "f", Namespaces.SpreadsheetML);
                cellNode.AppendChild(fNode);
                fNode.InnerText = formular;
                m_Package.SavePart(sheetUri, sheetXml);
            }
        }
        #endregion

        #region chart data binding
        /// <summary>
        /// 
        /// </summary>
        /// <param name="chartName"></param>
        /// <param name="title"></param>
        public void UpdateChartTitle(string chartName, string title)
        {
            {
                Uri chartUri =
                    new Uri(string.Format("/xl/charts/{0}.xml", chartName),
                            UriKind.Relative);
                XmlDocument chartXml = m_Package.GetWritablePart(chartUri);
                XmlNode titleNode = chartXml.SelectSingleNode("/c:chartSpace/c:chart/c:title/c:tx/c:rich/a:p/a:r/a:t", Namespaces.NamespaceManager);
                if (titleNode != null)
                {
                    titleNode.InnerText = title;
                }

                m_Package.SavePart(chartUri, chartXml);
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="chartName"></param>
        /// <param name="chartType"></param>
        /// <param name="categoryLabels"></param>
        public void UpdateChartStaticCategory(string chartName,
            ExcelChartType chartType, string[] categoryLabels)
        {
            string chartNodeName = "c:lineChart";
            if (chartType == ExcelChartType.PieChart)
                chartNodeName = "c:pieChart";
            else if (chartType == ExcelChartType.BarChart)
                chartNodeName = "c:barChart";
            {
                Uri chartUri =
                    new Uri(string.Format("/xl/charts/{0}.xml", chartName),
                            UriKind.Relative);
                XmlDocument chartXml = m_Package.GetWritablePart(chartUri);
                XmlNode categoryLabelNode = chartXml.SelectSingleNode(
                    string.Format("/c:chartSpace/c:chart/c:plotArea/{0}/c:ser[1]/c:cat/c:strLit", chartNodeName), Namespaces.NamespaceManager);
                if (categoryLabelNode != null)
                {
                    categoryLabelNode.RemoveAll();
                }
                XPathNavigator sheetData =
                    chartXml.CreateNavigator().SelectSingleNode(
                        string.Format(
                            "/c:chartSpace/c:chart/c:plotArea/{0}/c:ser[1]/c:cat/c:strLit",
                            chartNodeName),
                        Namespaces.NamespaceManager);
                using (XmlWriter writer = sheetData.AppendChild())
                {
                    writer.WriteStartElement(Prefixes.ChartML, "ptCount", Namespaces.ChartML);
                    writer.WriteAttributeString("val", categoryLabels.Length.ToString());
                    writer.WriteEndElement();//ptCount
                    for (int i = 0; i < categoryLabels.Length; i++)
                    {
                        writer.WriteStartElement(Prefixes.ChartML, "pt", Namespaces.ChartML);
                        writer.WriteAttributeString("idx", i.ToString());
                        writer.WriteStartElement(Prefixes.ChartML, "v", Namespaces.ChartML);
                        writer.WriteString(categoryLabels[i]);
                        writer.WriteEndElement();//v
                        writer.WriteEndElement();//pt
                    }
                }

                m_Package.SavePart(chartUri, chartXml);
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="chartName"></param>
        /// <param name="chartType"></param>
        /// <param name="categoryFormular"></param>
        public void UpdateChartCategoryFormular(string chartName, ExcelChartType chartType, string categoryFormular)
        {
            string chartNodeName = "c:lineChart";
            if (chartType == ExcelChartType.PieChart)
                chartNodeName = "c:pieChart";
            else if (chartType == ExcelChartType.BarChart)
                chartNodeName = "c:barChart";
            {
                Uri chartUri =
                    new Uri(string.Format("/xl/charts/{0}.xml", chartName),
                            UriKind.Relative);
                XmlDocument chartXml = m_Package.GetWritablePart(chartUri);
                XmlNode categoryLabelNode = chartXml.SelectSingleNode(
                    string.Format("/c:chartSpace/c:chart/c:plotArea/{0}/c:ser[1]/c:cat/c:strRef", chartNodeName), Namespaces.NamespaceManager);
                if (categoryLabelNode != null)
                {
                    XmlNode formularNode = categoryLabelNode.SelectSingleNode("c:f", Namespaces.NamespaceManager);
                    if (formularNode != null)
                        formularNode.InnerText = categoryFormular;
                }

                m_Package.SavePart(chartUri, chartXml);
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="chartName"></param>
        /// <param name="chartType"></param>
        /// <param name="seriesLabel"></param>
        /// <param name="seriesFormular"></param>
        public void UpdateChartSeriesFormular(
            string chartName, ExcelChartType chartType,
            string seriesLabel, string seriesFormular)
        {
            string chartNodeName = "c:lineChart";
            if (chartType == ExcelChartType.PieChart)
                chartNodeName = "c:pieChart";
            else if (chartType == ExcelChartType.BarChart)
                chartNodeName = "c:barChart";
            {
                Uri chartUri =
                    new Uri(string.Format("/xl/charts/{0}.xml", chartName),
                            UriKind.Relative);
                XmlDocument chartXml = m_Package.GetWritablePart(chartUri);
                XmlNodeList seriesLabelNodes = chartXml.SelectNodes(
                    string.Format("/c:chartSpace/c:chart/c:plotArea/{0}/c:ser/c:tx/c:v", chartNodeName), Namespaces.NamespaceManager);
                if (seriesLabelNodes != null && seriesLabelNodes.Count > 0)
                {
                    foreach (XmlNode seriesLabelNode in seriesLabelNodes)
                    {
                        if (seriesLabelNode.InnerText != null && seriesLabelNode.InnerText == seriesLabel)
                        {
                            XmlNode seriesNode = seriesLabelNode.ParentNode.ParentNode;
                            XmlNode seriesFormularNode = seriesNode.SelectSingleNode("c:val/c:numRef/c:f", Namespaces.NamespaceManager);
                            if (seriesFormularNode != null)
                                seriesFormularNode.InnerText = seriesFormular;
                            break;
                        }
                    }
                }

                m_Package.SavePart(chartUri, chartXml);
            }
        }

        /// <summary>
        /// need to save shared string first
        /// </summary>
        public void Save()
        {
            m_SharedString.Save(m_Package);
            m_Package.Save(this.m_ReportFilePath);
        }
        #endregion

        public void Dispose()
        {
            if (this.m_Package != null)
            {
                m_Package.Dispose();
                m_Package = null;
            }
        }
    }
}
